package com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.publicOperate.repository;

import com.css.fxfzfxqh.modules.buildings.calculationTasks.entity.CalculationTasks;
import com.css.fxfzfxqh.modules.buildings.probabilityEvaluation.publicOperate.entity.PsHazardDataValue;
import com.css.fxfzfxqh.modules.vulnerabilityData.entity.BuildingIndividualVulnerability;
import com.css.fxfzfxqh.util.PlatformObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Dbj
 * @date 2023年09月07日  14:42
 * 概率估计算中间公共操作、公共结果操作
 */
@Repository
public class PublicOperateRepository {

    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public List<PsHazardDataValue> getPsHazardDataValueList(String pirctureType, String geom) {
        String sql = "SELECT  t1.id,t1.geom geom,t2.im_value, t2.f_im_value  FROM" +
                " (SELECT ID,geom  FROM fxqh_ps_hazard_data " +
                " WHERE del_flag = '0' "+
                " AND ST_Intersects ( st_geometryfromtext('" + geom + "',4490), st_geometryfromtext ( 'POINT(' || longitude || ' ' || latitude || ')', 4490 ) ) ) t1 " +
                " inner JOIN fxqh_ps_hazard_data_value t2 ON t1.ID = t2.ph_id AND t2.type = '" +pirctureType +"'" +
                " ORDER BY t1.id,t2.im_value";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(PsHazardDataValue.class));
    }

    public String getYear( String geom, CalculationTasks tasks){
        String ldPga = "0".equals(tasks.getLdPga())?"1":"0";
        String mainTable = "";
        String childTable = "";
        if("0".equals(tasks.getOccurrenceProbabilityVersion())){
            //国家级
            mainTable = "fxqh_ps_hazard_data_country";
            childTable = "fxqh_ps_hazard_data_value_country";
        }else if("1".equals(tasks.getOccurrenceProbabilityVersion())){
            //自定义 0国家级管理员  1省级管理员  2市级管理员  3县级管理员
            if("0".equals(tasks.getRole())){
                mainTable = "fxqh_ps_hazard_data_country";
                childTable = "fxqh_ps_hazard_data_value_country";
            }else if("1".equals(tasks.getRole())){
                mainTable = "fxqh_ps_hazard_data_province";
                childTable = "fxqh_ps_hazard_data_value_province";
            }else if("2".equals(tasks.getRole())){
                mainTable = "fxqh_ps_hazard_data_city";
                childTable = "fxqh_ps_hazard_data_value_city";
            }else if("3".equals(tasks.getRole())){
                mainTable = "fxqh_ps_hazard_data_county";
                childTable = "fxqh_ps_hazard_data_value_county";
            }
        }
        if(StringUtils.isBlank(mainTable) || StringUtils.isBlank(childTable)){
            return "";
        }
        String sql = "SELECT  distinct(mas.year)  FROM "+mainTable+" mas inner join "+childTable+" sla" +
                " on mas.id = sla.ph_id and sla.type='" + ldPga + "'  ";
        if(StringUtils.isNotBlank(geom)){
            sql+=" AND ST_Intersects ( st_geometryfromtext('" + geom + "',4490), st_geometryfromtext ( 'POINT(' || longitude || ' ' || latitude || ')', 4490 ))";
        }
        List<String> list = jdbcTemplate.queryForList(sql, String.class);
        return PlatformObjectUtils.isNotEmpty(list) && list.size()>0 ? list.get(0):"";
    }
    public List<BuildingIndividualVulnerability> getBuildingIndividualVulnerabilityList(String longitude,String latitude){
        String sql = "select * from fxqh_building_individual_vulnerability where ST_Intersects ( st_geometryfromtext('POINT(' || "+longitude+"  || ' ' || "+latitude+" || ')',4490), st_geometryfromtext ( 'POINT(' || longitude || ' ' || latitude || ')', 4490 ) )";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(BuildingIndividualVulnerability.class));
    }
    public List<PsHazardDataValue> getPsHazardDataValueList1(String pirctureType, String geom, CalculationTasks tasks) {
        pirctureType = "0".equals(pirctureType)?"1":"0";
        String mainTable = "";
        String childTable = "";
        if("0".equals(tasks.getOccurrenceProbabilityVersion())){
           //国家级
            mainTable = "fxqh_ps_hazard_data_country";
            childTable = "fxqh_ps_hazard_data_value_country";
        }else if("1".equals(tasks.getOccurrenceProbabilityVersion())){
            //自定义 0国家级管理员  1省级管理员  2市级管理员  3县级管理员
            if("0".equals(tasks.getRole())){
                mainTable = "fxqh_ps_hazard_data_country";
                childTable = "fxqh_ps_hazard_data_value_country";
            }else if("1".equals(tasks.getRole())){
                mainTable = "fxqh_ps_hazard_data_province";
                childTable = "fxqh_ps_hazard_data_value_province";
            }else if("2".equals(tasks.getRole())){
                mainTable = "fxqh_ps_hazard_data_city";
                childTable = "fxqh_ps_hazard_data_value_city";
            }else if("3".equals(tasks.getRole())){
                mainTable = "fxqh_ps_hazard_data_county";
                childTable = "fxqh_ps_hazard_data_value_county";
            }
        }
        if(StringUtils.isBlank(mainTable) || StringUtils.isBlank(childTable)){
            return new ArrayList<>();
        }
        String sql = "SELECT  t1.id,t1.kilometer_grid_id,t1.geom geom,t2.im_value, t2.f_im_value  FROM" +
                " (SELECT id,kilometer_grid_id,geom  FROM  " +mainTable +
                " WHERE del_flag = '0'   ";
        if("1".equals(tasks.getOccurrenceProbabilityVersion())){
            sql+=" and province='"+tasks.getAssessProvince()+"' ";
        }else if("2".equals(tasks.getOccurrenceProbabilityVersion())){
            sql+=" and province='"+tasks.getAssessProvince()+"' and city='"+tasks.getAssessCity()+"' ";
        }else if("3".equals(tasks.getOccurrenceProbabilityVersion())){
            sql+=" and province='"+tasks.getAssessProvince()+"' and city='"+tasks.getAssessCity()+"' and county='"+tasks.getAssessCounty()+"' ";
        }
        String appendSql = "";
        if(StringUtils.isNotBlank(geom)){
            appendSql+=" AND ST_Intersects ( st_geometryfromtext('" + geom + "',4490), st_geometryfromtext ( 'POINT(' || longitude || ' ' || latitude || ')', 4490 ) ) ";
        }
        appendSql+="  ) t1 " +
                " inner JOIN "+childTable+" t2 ON t1.ID = t2.ph_id AND t2.type = '" +pirctureType +"'" +
                " ORDER BY t1.kilometer_grid_id,t2.im_value";
        sql+=appendSql;
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(PsHazardDataValue.class));
    }

    public List<PsHazardDataValue> getFSGLbyLonAndLat(Double lon, Double lat,String pirctureType) {
        String sql = "SELECT  t1.kilometer_grid_id,t2.im_value, t2.f_im_value  FROM" +
                " (SELECT ID,kilometer_grid_id,geom  FROM fxqh_ps_hazard_data " +
                " WHERE del_flag = '0' "+
                " AND ST_Intersects ( st_geometryfromtext( geom ,4490), st_geometryfromtext ( 'POINT(' || "+lon+" || ' ' || "+lat+" || ')', 4490 ) ) ) t1 " +
                " LEFT JOIN fxqh_ps_hazard_data_value t2 ON t1.ID = t2.ph_id AND t2.type = '" +pirctureType +"'" +
                " ORDER BY t1.kilometer_grid_id,t2.im_value";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(PsHazardDataValue.class));
    }

    public List<PsHazardDataValue> getFSGLbyLonAndLat1(Double lon, Double lat,String pirctureType, CalculationTasks tasks) {
        String mainTable = "";
        String childTable = "";
        if("0".equals(tasks.getOccurrenceProbabilityVersion())){
            //国家级
            mainTable = "fxqh_ps_hazard_data_country";
            childTable = "fxqh_ps_hazard_data_value_country";
        }else if("1".equals(tasks.getOccurrenceProbabilityVersion())){
            //自定义 0国家级管理员  1省级管理员  2市级管理员  3县级管理员
            if("0".equals(tasks.getRole())){
                mainTable = "fxqh_ps_hazard_data_country";
                childTable = "fxqh_ps_hazard_data_value_country";
            }else if("1".equals(tasks.getRole())){
                mainTable = "fxqh_ps_hazard_data_province";
                childTable = "fxqh_ps_hazard_data_value_province";
            }else if("2".equals(tasks.getRole())){
                mainTable = "fxqh_ps_hazard_data_city";
                childTable = "fxqh_ps_hazard_data_value_city";
            }else if("3".equals(tasks.getRole())){
                mainTable = "fxqh_ps_hazard_data_county";
                childTable = "fxqh_ps_hazard_data_value_county";
            }
        }
        if(StringUtils.isBlank(mainTable) || StringUtils.isBlank(childTable)){
            return new ArrayList<>();
        }
        String sql = "SELECT  t1.id,t1.geom geom,t2.im_value, t2.f_im_value  FROM" +
                " (SELECT id,geom  FROM  " +mainTable +
                " WHERE del_flag = '0'  ";
        if("1".equals(tasks.getOccurrenceProbabilityVersion())){
            sql+=" and province='"+tasks.getAssessProvince()+"' ";
        }else if("2".equals(tasks.getOccurrenceProbabilityVersion())){
            sql+=" and province='"+tasks.getAssessProvince()+"' and city='"+tasks.getAssessCity()+"' ";
        }else if("3".equals(tasks.getOccurrenceProbabilityVersion())){
            sql+=" and province='"+tasks.getAssessProvince()+"' and city='"+tasks.getAssessCity()+"' and county='"+tasks.getAssessCounty()+"' ";
        }
        String appendSql= " AND ST_Intersects ( geom, st_geometryfromtext ( 'POINT(' || "+lon+" || ' ' || "+lat+" || ')', 4490 ) ) ) t1 " +
                " inner JOIN "+childTable+" t2 ON t1.ID = t2.ph_id AND t2.type = '" +pirctureType +"'" +
                " ORDER BY t1.id,t2.im_value";
        sql+=appendSql;
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(PsHazardDataValue.class));
    }
}
